<?php

namespace app\admin\controller;

use app\admin\model\Admin;
use app\admin\model\User;
use app\common\controller\Backend;
use app\common\model\Attachment;
use fast\Date;
use think\Db;
use app\admin\model\store\Shop;
use app\admin\model\service\Service;
use app\admin\model\staff\Staff;
use app\admin\model\wanlshop\Order;
use app\admin\model\reservation\Reservation;
use think\Session;

/**
 * 财务概况
 *
 * @icon   fa fa-dashboard
 * @remark 用于展示当前系统中的统计数据、统计报表及重要实时数据
 */
class Finance extends Backend
{

    /**
     * 查看
     */
    public function index()
    {
        try {
            \think\Db::execute("SET @@sql_mode='';");
        } catch (\Exception $e) {

        }
        $this->request->filter(['strip_tags']);
        $starttime = strtotime($this->request->request('starttime'));
        $endtime = strtotime($this->request->request('endtime'));

        //获取支付方式 
        $payways = [];  
        $payways[0] = Db::name('pay')->where('type','0')->column('id');
        $payways[1] = Db::name('pay')->where('type','1')->column('id');
        $payways[2] = Db::name('pay')->where('type','2')->column('id');

        $column = [];
        if (!$starttime) {
            $starttime = Date::unixtime('day', 0);
            $endtime = Date::unixtime('day', 0, 'end');
        }


        $joinlist = Db("user")->where('jointime', 'between time', [$starttime, $endtime])
            ->field('jointime, status, COUNT(*) AS nums, DATE_FORMAT(FROM_UNIXTIME(jointime), "%Y-%m-%d") AS join_date')
            ->group('join_date')
            ->select();
        for ($time = $starttime; $time <= $endtime; ) {
            $column[] = date("Y-m-d", $time);
            $time += 86400;
        }
        $userlist = array_fill_keys($column, 0);
        foreach ($joinlist as $k => $v) {
            $userlist[$v['join_date']] = $v['nums'];
        }

        $dbTableList = Db::query("SHOW TABLE STATUS");
        $addonList = get_addon_list();
        $totalworkingaddon = 0;
        $totaladdon = count($addonList);
        foreach ($addonList as $index => $item) {
            if ($item['state']) {
                $totalworkingaddon += 1;
            }
        }


        $shop_id = Session::get('shop');
        $where2 = [];
        $where3 = [];
        $where2['o.data_type'] = ['<>','merchant'];
        if ($shop_id != 1) {
            $where2['o.shop_id'] = $shop_id;
            $where3['shop_id'] = $shop_id;
        }

        $data1 = Db::name("wanlshop_order")->alias('o')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            //->where('o.shop_id','=',$shop_id)
            ->where($where2)
            ->where('o.state', 'in', '2,3,4,5,6')
            ->where('g.gdtype', '=', '0')
            ->join('wanlshop_order_goods g', 'g.order_id=o.id', 'left')
            ->where('g.id', '>', 0)
            ->field('DATE_FORMAT(FROM_UNIXTIME(o.createtime), "%Y-%m-%d") AS o_date,sum(g.actual_payment  * g.number) as o_sum,count(distinct o.id) as o_no')
            ->group('o_date')
            ->select();

        $data2 = Db::name("wanlshop_order")->alias('o')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            // ->where('o.shop_id','=',$shop_id)
            ->where($where2)
            ->where('o.state', 'in', '2,3,4,5,6')
            ->where('g.gdtype', '=', '1')
            ->join('wanlshop_order_goods g', 'g.order_id=o.id', 'left')
            ->where('g.id', '>', 0)
            ->field('DATE_FORMAT(FROM_UNIXTIME(o.createtime), "%Y-%m-%d") AS o_date,sum(g.actual_payment  * g.number) as o_sum,count(distinct o.id) as o_no')
            ->group('o_date')
            ->select();

        $data3 = Db::name("wanlshop_order")->alias('o')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            //->where('o.shop_id','=',$shop_id)
            ->where($where2)
            ->where('o.state', 'in', '2,3,4,5,6')
            //->where('g.gdtype','=','1')
            ->join('wanlshop_order_item g', 'g.order_id=o.id', 'left')
            ->where('g.id', '>', 0)
            ->field('DATE_FORMAT(FROM_UNIXTIME(o.createtime), "%Y-%m-%d") AS o_date,sum(g.actual_price) as o_sum,count(distinct o.id) as o_no')
            ->group('o_date')
            ->select();

        $data4 = Db::name("wanlshop_order")->alias('o')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            // ->where('o.shop_id','=',$shop_id)
            ->where($where2)
            ->where('o.state', 'in', '2,3,4,5,6')
            //->where('g.gdtype','=','0')
            //->join('wanlshop_order_goods g','g.order_id=o.id','left')
            ->field('DATE_FORMAT(FROM_UNIXTIME(o.createtime), "%Y-%m-%d") AS o_date,count(distinct o.id) as o_no')
            ->group('o_date')
            ->select();

        $orderlist = array_fill_keys($column, 0);
        $order_slist = array_fill_keys($column, 0);
        foreach ($data1 as $k => $v) {
            $orderlist[$v['o_date']] = $v['o_no'];
            $order_slist[$v['o_date']] = $v['o_sum'];
        }
        $orderlist2 = array_fill_keys($column, 0);
        $order_slist2 = array_fill_keys($column, 0);
        foreach ($data2 as $k => $v) {
            $orderlist2[$v['o_date']] = $v['o_no'];
            $order_slist2[$v['o_date']] = $v['o_sum'];
        }
        $orderlist3 = array_fill_keys($column, 0);
        $order_slist3 = array_fill_keys($column, 0);
        foreach ($data3 as $k => $v) {
            $orderlist3[$v['o_date']] = $v['o_no'];
            $order_slist3[$v['o_date']] = $v['o_sum'];
        }
        $orderlist4 = array_fill_keys($column, 0);
        foreach ($data4 as $k => $v) {
            $orderlist4[$v['o_date']] = $v['o_no'];
            //$order_slist3[$v['o_date']] = $v['o_sum'];
        }
        $orderlist5 = array_fill_keys($column, 0);
        foreach ($data4 as $k => $v) {
            $orderlist5[$v['o_date']] = $userlist[$v['o_date']] + $v['o_no'];
            //$order_slist3[$v['o_date']] = $v['o_sum'];
        }

        $list1 = Db::name('wanlshop_order_goods')->alias('i')->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('i.title,sum(i.actual_payment  * i.number) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where('o.state', '6')->where('i.gdtype', '=', '0')
            ->where($where2)
            ->limit(0, 10)
            ->group('i.title')
            ->order('i_sum desc')
            ->select();
        $list2 = Db::name('wanlshop_order_goods')->alias('i')->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('i.title,count(distinct i.order_id) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where('o.state', '6')->where('i.gdtype', '=', '0')
            ->where($where2)
            ->limit(0, 10)
            ->group('i.title')
            ->order('i_sum desc')
            ->select();
        $list3 = Db::name('wanlshop_order_goods')->alias('i')->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('i.title,sum(i.actual_payment  * i.number) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where('o.state', '6')->where('i.gdtype', '=', '1')
            ->where($where2)
            ->limit(0, 10)
            ->group('i.title')
            ->order('i_sum desc')
            ->select();
        $list4 = Db::name('wanlshop_order_goods')->alias('i')->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('i.title,count(distinct i.order_id) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where('o.state', '6')->where('i.gdtype', '=', '1')
            ->where($where2)
            ->limit(0, 10)
            ->group('i.title')
            ->order('i_sum desc')
            ->select();
        $list5 = Db::name('wanlshop_order_item')->alias('i')->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('i.name as title,sum(i.actual_price) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where($where2)
            ->where('o.state', 'in', '6')
            ->limit(0, 10)
            ->group('i.name')
            ->order('i_sum desc')
            ->select();
        $list6 = Db::name('wanlshop_order_item')->alias('i')->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('i.name as title,count(distinct i.order_id) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where($where2)
            ->where('o.state', '6')
            ->limit(0, 10)
            ->group('i.name')
            ->order('i_sum desc')
            ->select();

        $list7 = Db::name('wanlshop_order')->alias('o')
            ->join('wanlshop_pay_out_trade t', 'o.id=t.order_id', 'left')
            ->join('pay p', 'p.id=t.pay_way_id', 'left')
            ->field('case  when p.type=1 then "余额" when p.type=2 then "景珍支付" else "记账" end as name ,sum(o.actual_price) as value')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where($where2)
            ->where('o.state', '6')
            ->limit(0, 10)
            ->group('case  when p.type=1 then "余额" when p.type=2 then "景珍支付" else "记账" end')
            ->order('value desc')
            ->select();

        $list9 = $list7;

        $list8 = Db::name('wanlshop_order')->alias('o')
            ->join('wanlshop_pay_out_trade t', 'o.id=t.order_id', 'left')
            ->join('pay p', 'p.id=t.pay_way_id', 'left')
            //->field('case  when p.type=1 then "余额" when p.type=2 then "景珍支付" else "记账" end as payname ,sum(o.actual_price) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where($where2)
            ->where('o.state', '6')
            ->sum('o.actual_price');

        foreach ($list7 as $k => $row) {
            if ($list8 == 0) {
                $row['i_sum'] = "0%";
            } else {
                $row['i_sum'] = number_format($row['value'] / $list8 * 100, 1) . '%';
            }
            $list7[$k] = $row;
        }

        $list10 = Db::name('wanlshop_order_item')->alias('i')
            ->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            ->field('case  when i.type=4 then "充值卡" when i.type=5 then "折扣卡" else "次卡" end as name ,sum(i.actual_price) as value,count(o.id) as sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where('o.state',  '6')
            ->where($where2)
            ->limit(0, 10)
            ->group('case  when i.type=4 then "充值卡" when i.type=5 then "折扣卡" else "次卡" end')
            ->order('value desc')
            ->select();

        $list12 = $list10;

        $list11 = Db::name('wanlshop_order_item')->alias('i')
            ->join('wanlshop_order o', 'o.id=i.order_id', 'left')
            //->field('case  when p.type=1 then "余额" when p.type=2 then "景珍支付" else "记账" end as payname ,sum(o.actual_price) as i_sum')
            ->where('o.createtime', 'between time', [$starttime, $endtime])
            ->where('o.state', '6')
            ->sum('i.actual_price');

        foreach ($list10 as $k => $row) {
            if ($list11 == 0) {
                $row['i_sum'] = "0%";
            } else {
                $row['i_sum'] = number_format($row['value'] / $list11 * 100, 1) . '%';
            }

            $list10[$k] = $row;
        }

        $conditions = [];
        $_payways = array_unique(array_merge($payways[0],$payways[2]));

        // 子查询1：筛选符合条件的订单
        $filteredOrders = Db::name('wanlshop_order')
            ->alias('o')
            ->field('DISTINCT o.id AS order_id')
            ->join('fa_wanlshop_order_goods i', 'o.id = i.order_id')
            ->where('o.createtime', 'BETWEEN', [$starttime, $endtime])
            ->where('o.state', 6)
            ->where('i.gdtype', '1')
            ->buildSql();
            
        // 子查询2：聚合支付金额
        $paymentAmount = Db::name('wanlshop_pay_out_trade')
            ->field('order_id, SUM(price) AS total_amount')
            ->whereIn('pay_way_id', $_payways)
            ->group('order_id')
            ->buildSql();
            
        // 主查询
        $list22 = Db::table($filteredOrders . ' filtered_orders')
            ->join($paymentAmount . ' t', 'filtered_orders.order_id = t.order_id')
            ->field('SUM(COALESCE(t.total_amount, 0)) AS total_payment_amount')
            ->find();

        // 子查询1：筛选符合条件的订单
        $filteredOrders = Db::name('wanlshop_order')
            ->alias('o')
            ->field('DISTINCT o.id AS order_id')
            ->join('fa_wanlshop_order_goods i', 'o.id = i.order_id')
            ->where('o.createtime', 'BETWEEN', [$starttime, $endtime])
            ->where('o.state', 6)
            ->where('i.gdtype', '0')
            ->buildSql();


        $list23 = Db::table($filteredOrders . ' filtered_orders')
            ->join($paymentAmount . ' t', 'filtered_orders.order_id = t.order_id')
            ->field('SUM(COALESCE(t.total_amount, 0)) AS total_payment_amount')
            ->find();

        
        //当日卡项订单笔数+商品订单笔数+服务订单笔数
        $total_real_count = Db::name('wanlshop_order')->alias('o')
                ->join('wanlshop_pay_out_trade t', 'o.id=t.order_id', 'left')
                ->join('pay p', 'p.id=t.pay_way_id', 'left')
                ->where($where2)
                ->where('o.createtime', 'between time', [$starttime, $endtime])
                ->where('p.type','<>','1')
                ->where('o.state', '6')
                ->count('distinct o.id');
        //当日卡项（次卡+充值卡）实收金额+商品实收金额+服务订单实收金额
        $total_real_amount = Db::name('wanlshop_order')->alias('o')
                ->join('wanlshop_pay_out_trade t', 'o.id=t.order_id', 'left')
                ->join('pay p', 'p.id=t.pay_way_id', 'left')
                ->where($where2)
                ->where('o.createtime', 'between time', [$starttime, $endtime])
                ->where('p.type','<>','1')
                ->where('o.state', '6')
                ->sum('t.price');
        $user_amount = 0;
        if ($total_real_count != 0) {
            $user_amount = number_format($total_real_amount / $total_real_count, 2);
        }
        $this->view->assign([
            'totaluser' => User::count(),
            'totaladdon' => $totaladdon,
            'totaladmin' => Admin::count(),
            'totalcategory' => \app\common\model\Category::count(),
            'todayusersignup' => User::whereTime('jointime', 'today')->count(),
            'todayuserlogin' => User::whereTime('logintime', 'today')->count(),
            'sevendau' => User::whereTime('jointime|logintime|prevtime', '-7 days')->count(),
            'thirtydau' => User::whereTime('jointime|logintime|prevtime', '-30 days')->count(),
            'threednu' => User::whereTime('jointime', '-3 days')->count(),
            'sevendnu' => User::whereTime('jointime', '-7 days')->count(),
            'dbtablenums' => count($dbTableList),
            'dbsize' => array_sum(array_map(function ($item) {
                return $item['Data_length'] + $item['Index_length'];
            }, $dbTableList)),
            'totalworkingaddon' => $totalworkingaddon,
            'attachmentnums' => Attachment::count(),
            'attachmentsize' => Attachment::sum('filesize'),
            'picturenums' => Attachment::where('mimetype', 'like', 'image/%')->count(),
            'picturesize' => Attachment::where('mimetype', 'like', 'image/%')->sum('filesize'),
            'starttime' => $starttime,
            'endtime' => $endtime,
            // 'pay' => Order::where('createtime', 'between time', [$starttime, $endtime])
            //     ->where('state', 'in', '2,3,4,5,6')
            //     ->sum('actual_price'),
            'pay' => Db::name('wanlshop_order')->alias('o')
                    ->join('wanlshop_pay_out_trade t', 'o.id=t.order_id', 'left')
                    ->join('pay p', 'p.id=t.pay_way_id', 'left')
                    ->where('o.createtime', 'between time', [$starttime, $endtime])
                    ->where($where2)
                    ->where('p.type','<>','1')
                    ->where('o.state', 6)
                    ->sum('o.actual_price'),


            'orderuser' => Order::where('createtime', 'between time', [$starttime, $endtime])
                        ->where('state', 'in', '2,3,4,5,6')
                        ->count('distinct user_id'),

            'pay_1' => Db::name('wanlshop_order')->alias('o')
                    ->join('wanlshop_order_item_pay p', 'o.id=p.order_id', 'left')
                    ->where('o.createtime', 'between time', [$starttime, $endtime])
                    ->where($where2)
                    ->where('o.state', 6)
                    ->sum('p.money'),

            'pay_2' => Db::name('wanlshop_order_item')->alias('i')
                ->join('wanlshop_order o', 'o.id=i.order_id', 'left')
                ->where('o.createtime', 'between time', [$starttime, $endtime])
                ->where($where2)
                ->where('o.state', 6)
                ->sum('i.actual_price'),

            // 'pay_3' => Db::name('wanlshop_order_goods')->alias('i')->join('wanlshop_order o','o.id=i.order_id','left')->where('o.createtime', 'between time', [$starttime, $endtime])
            // ->where('o.state','in','2,3,4,5,6')->where('i.gdtype','=','1')->sum('i.actual_payment'),
            // 'pay_3' => $list22['0']['sum'] ? $list22['0']['sum'] : 0,
            // 'pay_4' => $list23['0']['sum'] ? $list23['0']['sum'] : 0,
            'pay_3' => $list22['total_payment_amount'] ?? 0,
            'pay_4' => $list23['total_payment_amount'] ?? 0,
            // 'pay_4' => Db::name('wanlshop_order_goods')->alias('i')->join('wanlshop_order o','o.id=i.order_id','left')->where('o.createtime', 'between time', [$starttime, $endtime])
            // ->where('o.state','in','2,3,4,5,6')->where('i.gdtype','=','0')->sum('i.actual_payment'),

            'user_1' => $user_amount,
            'user_2' => User::where('jointime', 'between time', [$starttime, $endtime])->where($where3)->count(),
            'user_3' => Order::where('createtime', 'between time', [$starttime, $endtime])
                    ->where($where3)->where('state', 'in', '2,3,4,5,6')
                    ->count('distinct user_id'),
            'user_4' => Db::name('wanlshop_order_item')->alias('i')
                        ->join('wanlshop_order o', 'o.id=i.order_id', 'left')
                        ->where($where2)
                        ->where('o.createtime', 'between time', [$starttime, $endtime])
                        ->where('o.state', 6)
                        ->where('i.type', '4')
                        ->count('distinct o.user_id'),

            'user_5' => 0,
            // 'user_5' => Reservation::where('createtime', 'between time', [$starttime, $endtime])->count(),

            'i_1' => $list1,
            'i_2' => $list2,
            'i_3' => $list3,
            'i_4' => $list4,
            'i_5' => $list5,
            'i_6' => $list6,
            'i_7' => $list7,
            'i_10' => $list10,



        ]);

        $this->assignconfig('starttime', $starttime);
        $this->assignconfig('endtime', $endtime);
        $this->assignconfig('column', array_keys($userlist));
        $this->assignconfig('userdata', array_values($userlist));
        $this->assignconfig('orderdata', array_values($orderlist));
        $this->assignconfig('order_sdata', array_values($order_slist));
        $this->assignconfig('orderdata2', array_values($orderlist2));
        $this->assignconfig('order_sdata2', array_values($order_slist2));
        $this->assignconfig('orderdata3', array_values($orderlist3));
        $this->assignconfig('order_sdata3', array_values($order_slist3));
        $this->assignconfig('orderdata4', array_values($orderlist4));
        $this->assignconfig('orderdata5', array_values($orderlist5));

        $this->assignconfig('i_9', $list9);
        $this->assignconfig('i_12', $list12);

        return $this->view->fetch();
    }

}
